

local file_path_in "/tochange/"


	** load 2009 sales data
	use "`file_path_in'/main_specification2.dta" , clear 
	sort apnold RECORDING_DATE
	duplicates tag apnold, gen(tag)
	drop if tag>0
	rename apnold apn
	rename zip zipcode
	tostring fips, gen(fips_code)
	replace fips_code = "0"+fips_code if strlen(fips_code)==4
	rename APN_SEQUENCE_NUMBER apn_sequence_num
	save "`file_path_in'//sales_tomerge2.dta" , replace

	** load listings data
	use "`file_path_in'//county transactions/listings_newhomes_cleaned.dta", clear
	rename CMAS_PARCEL_ID apn   
	drop if apn==""
	sort apn listdate
	order apn* listdate addr

	merge m:1 apn  using "`file_path_in'//sales_tomerge2.dta"
	tab _merge
	keep if _merge==3
	save "`file_path_in'//alllistings_main_specification2.dta", replace
	
	use "`file_path_in'//alllistings_main_specification2.dta", clear
	order apn listdate closedate RECORD addr ListingStatusCode saleam ClosePrice _m  PROPERTY_HOUSE_NUMBER PROPERTY_STREET_NAME  zipcode CMAS_Zip5
	
	** filters **
	keep if closedate!=. & ClosePrice!=. /* only look at sales */
	
	drop if list_dt>RECORDING_DATE  /* don't want listings of home following the builder sale */

	** keep sales only 
	keep if ListingStatusCode=="Closed Sale" | ListingStatusCode=="Closed" | ListingStatusCode=="Sold"  | ListingStatusCode=="" | ListingStatusCode=="SOLD" | ListingStatusCode== "SOLD-INNER OFFICE"
	
	** drop listings before 2009
	drop if closedate<td(01jan2009)
	
	** get rid of duplicate listings (usually where close date differs by just one day, or where ListingStatusCode says "Closed" and then "Sale"
	set sortseed 56731
	gsort -apn listdate  address ClosePrice
	bysort apn listdate  address ClosePrice: gen count = _n
	keep if count==1

	** identify total number of listings against home: keep obs with just one listing per sale
	gen closed = 1
	set sortseed 56731
	gsort apn
	bysort apn : gen nvals = sum(closed)
	bysort apn: replace nvals = nvals[_N] 
	drop if nvals>1
	
	* check each apn corresponds to just one observed sale
	isid apn
	
	save "`file_path_in'//alllistings_for_regs2.dta", replace

	reghdfe time_mkt shockdelta1yrZIL sqfeet baths i.PROPERTY_INDICATOR_CODE loghpZIL_fips if !mi(lnsaleamt) & !mi(sqfeet) & !mi(baths) & !mi(PROPERTY_INDICATOR_CODE), absorb(i.zipcode##i.year) vce(cluster fips)
	reghdfe lnsaleamt shockdelta1yrZIL sqfeet baths i.PROPERTY_INDICATOR_CODE loghpZIL_fips if !mi(time_mkt) & !mi(sqfeet) & !mi(baths) & !mi(PROPERTY_INDICATOR_CODE), absorb(i.zipcode##i.year) vce(cluster fips)


